﻿use QLRAPPHIM
--------------------
create Procedure sp_LostUpdate_error
@MaVe varchar(10),
@LoaiVe bit,
@HoTenKH varchar(50),
@SDT nchar(11),
@CMND nchar(9),
@NgayDat datetime,
@output int output
as
begin
 begin tran lostupdate_error

declare @tinhtrang int
select @tinhtrang = TinhTrangVe from Ve 
where MaVe = @MaVe

waitfor  DELAY '00:00:10'
if (@tinhtrang = 0)
begin
update Ve set TinhTrangVe = 1,NguoiLon_TreEm = @LoaiVe ,HoTenKH = @HoTenKH,SDT = @SDT,CMND = @CMND,ThoiGianDatVe = @NgayDat where MaVe = @MaVe
 set @output = 1
end
else
set @output = 0
commit tran lostupdate_error
end
Go

-------------------------------------------------------------

create Procedure sp_LostUpdate_fix
@MaVe varchar(10),
@LoaiVe bit,
@HoTenKH varchar(50),
@SDT nchar(11),
@CMND nchar(9),
@NgayDat datetime,
@output int output
as
begin
 begin tran lostupdate_fix
set  transaction isolation level  repeatable read 
declare @tinhtrang int
select @tinhtrang = TinhTrangVe from Ve with(UPDLOCK)
where MaVe = @MaVe

waitfor  DELAY '00:00:10'
if @tinhtrang = 0 
begin

update Ve set TinhTrangVe = 1,NguoiLon_TreEm = @LoaiVe,HoTenKH = @HoTenKH,SDT = @SDT,CMND = @CMND,ThoiGianDatVe = @NgayDat where MaVe = @MaVe
 set @output =  1
end
else
set @output = 0

commit tran lostupdate_fix
end
-------------------------------
Go

create Procedure sp_LostUpdateDeadlock_erorr
@MaVe varchar(10),
@LoaiVe bit,
@HoTenKH varchar(50),
@SDT nchar(11),
@CMND nchar(9),
@NgayDat datetime,
@output int output
as
begin
 begin tran lostupdate_error
set  transaction isolation level  serializable 
declare @tinhtrang int
select @tinhtrang = TinhTrangVe from Ve
where MaVe = @MaVe

waitfor  DELAY '00:00:10'
if @tinhtrang = 0 
begin

update Ve set TinhTrangVe = 1,NguoiLon_TreEm = @LoaiVe,HoTenKH = @HoTenKH,SDT = @SDT,CMND = @CMND,ThoiGianDatVe = @NgayDat where MaVe = @MaVe
 set @output = 1
end
else
set @output = 0

commit tran lostupdate_error
end

-----
go
create Procedure sp_search
@soPhong int,
@tenPhim nvarchar(1000),
@gioChieu datetime,
@kq int = null output
as
select ROW_NUMBER() OVER (ORDER BY v.MaVe) AS STT,v.MaVe,v.STTChoNgoi,v.TinhTrangVe,v.NguoiLon_TreEm,v.MaCTSuatChieu,v.MaDayPC,v.HoTenKH,v.SDT,v.CMND from Ve v,Phim p,SuatChieu s,DayGhe_PhongChieu d,CTSuatChieu c
 where v.MaDayPC = d.MaDayPC and d.SoPhong = @soPhong and v.MaCTSuatChieu = c.MaCTSuatChieu and c.MaPhim = p.MaPhim and p.TenPhim = @tenPhim and c.MaSuatChieu  = s.MaSuatChieu 
 and s.ThoiGianBatDau = @gioChieu
return 1
go
exec sp_search '1',N'Sống Còn','4/20/2012 8:30 PM


